# -*- coding: UTF-8 -*-

import re
import datetime
import sys
from sqlalchemy import and_,  func, or_
from typing import Dict, List, Any, Generator

from sqlalchemy import Boolean, Column, DateTime, Integer, String, Index, Table, ForeignKey, text, JSON, TEXT, FLOAT
from sqlalchemy.orm import Session, relationship
from common.database import Base
from common.database import SessionMixin
from common.utils import is_empty
from core.logger import logger

__all__ = ['QosServiceDate']

class QosServiceDate(Base, SessionMixin):
    '''
    应用服务的质量信息
    '''
    __tablename__ = "qos_service_date"
    quality_date = Column(String(64), nullable=False, index=True, comment='数据日期')
    name = Column(String(256), nullable=False, index=True, comment='服务名称')
    # 服务器部署 or 容器部署
    classes = Column(String(128), nullable=False, index=True, comment='部署归类')
    department = Column(String(128), nullable=False, index=True, comment='所属部门')
    cpu_requests = Column(FLOAT, nullable=False, default=0, comment='cpu最低配额')
    cpu_limits = Column(FLOAT, nullable=False, default=0, comment='cpu最高配额')
    mem_requests = Column(FLOAT, nullable=False, default=0, comment='内存最低配额')
    mem_limits = Column(FLOAT, nullable=False, default=0, comment='内存最高配额')
    number = Column(Integer, nullable=False, default=0, comment='服务数量')
    livability = Column(FLOAT, nullable=False, default=0, comment='应用服务存活率')
    cpu_level = Column(String(64), nullable=False, index=True, default="低(cpu平均使用率<5%)", comment='cpu资源使用等级')
    cpu_usage_max = Column(FLOAT, nullable=False, default=0, comment='cpu使用率最大值')
    cpu_usage_min = Column(FLOAT, nullable=False, default=0, comment='cpu使用率最小值')
    cpu_usage_avg = Column(FLOAT, nullable=False, default=0, comment='cpu使用率平均值')
    cpu_prom_data = Column(JSON, nullable=False, default=[], comment='cpu监控数据')
    mem_level = Column(String(64), nullable=False, index=True, default="低(mem平均使用率<10%)", comment='内存资源使用等级')
    mem_usage_max = Column(FLOAT, nullable=False, default=0, comment='内存使用率最大值')
    mem_usage_min = Column(FLOAT, nullable=False, default=0, comment='内存使用率最小值')
    mem_usage_avg = Column(FLOAT, nullable=False, default=0, comment='内存使用率平均值')
    mem_prom_data = Column(JSON, nullable=False, default=[], comment='内存监控数据')
    comment = Column(String(256), nullable=True, comment='数据说明')



    __table_args__ = (
        Index('idx_qos_service_date_quality_date_name', 'quality_date', 'name', unique=True),
        {
            'mysql_engine': 'innodb',
            'mysql_charset': 'utf8mb4',
            'mysql_collate': 'utf8mb4_unicode_ci',
            'extend_existing': True,
            'mysql_row_format': 'dynamic'
        }
    )

    def __repr__(self):
        return f"<qos_service_date='{self.name}>"


    def object_to_dict(self, db: Session):
        result = {
            key: getattr(self, key)
            for key in self.__mapper__.c.keys()
        }
        return result



    # 删除一个日期的监控数据，重新入库
    @classmethod
    def delete_by_date(cls, db: Session, quality_date: str):
        db.query(cls).filter(
            cls.quality_date == quality_date
        ).delete()
        db.commit()



    # 日期内, 根据field字段模糊查询value
    @classmethod
    def get_server_date_search(cls, db: Session, quality_date: str, page: int, per_page: int, field: str, value: str):
        try:
            search_field = []
            display_field = cls.get_display_field()
            if value or field != "all":
                for key, col in cls.__table__.c.items():
                    if field == "all" or key == field:
                        search_field.append(col.like(f"%{value}%"))

            result = db.query(cls).filter(
                cls.quality_date == quality_date,
                or_(*search_field)
            ).with_entities(
                *display_field
            ).paginate(page=page, per_page=per_page)
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        # 每个字段的说明
        result["fields"] = cls.get_display_field_meta()

        return result



    # 日期内, 根据field字段聚合
    @classmethod
    def get_server_date_aggregate(cls, db: Session, quality_date: str, classes: str, field: str):
        try:
            search_field = [cls.quality_date == quality_date]
            if classes != "all":
                search_field.append(cls.classes == classes)

            display_field = [cls.quality_date]
            for key, col in cls.__table__.c.items():
                if key == field:
                    group_by_field = col
                    display_field.append(col)
                    break
            else:
                logger.error(f'get_server_date_aggregate, field:{field} not in table')
                return False, None

            data = db.query(cls).filter(
                *search_field
            ).with_entities(
                *display_field,
                func.count(cls.name).label('server_count')
            ).group_by(group_by_field).order_by("server_count").to_dict()
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return False, None

        return True, data



    # 日期内, 根据field字段排序
    @classmethod
    def get_server_date_sort(cls, db: Session, quality_date: str, classes: str, field: str):
        try:
            search_field = [cls.quality_date == quality_date]
            if classes != "all":
                search_field.append(cls.classes == classes)

            display_field = [
                cls.quality_date,
                cls.name,
                cls.classes,
                cls.department
            ]
            for key, col in cls.__table__.c.items():
                if key == field:
                    sort_field = col
                    display_field.append(col)
                    break
            else:
                logger.error(f'get_server_date_sort, field:{field} not in table')
                return False, None

            data = db.query(cls).filter(
                *search_field
            ).with_entities(
                *display_field
            ).order_by(sort_field.asc()).limit(10).to_dict()
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return False, None

        return True, data



    # 日期内, 根据field字段排序
    @classmethod
    def get_server_date_sort_livability(cls, db: Session, quality_date: str, classes: str):
        try:
            search_field = [
                cls.quality_date == quality_date,
                cls.livability != 100
            ]
            if classes != "all":
                search_field.append(cls.classes == classes)

            display_field = [
                cls.quality_date,
                cls.name,
                cls.classes,
                cls.department,
                cls.livability
            ]

            data = db.query(cls).filter(
                *search_field
            ).with_entities(
                *display_field
            ).order_by(cls.livability.asc()).limit(10).to_dict()
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return False, None

        return True, data
    
    
    
    # 获取一个服务最近7天的监控信息
    @classmethod
    def get_server_date_week(cls, db: Session, server_name: str, quality_dates: List[str]):
        try:
            data = db.query(cls).filter(
                cls.quality_date.in_(quality_dates),
                cls.name == server_name
            ).with_entities(
                cls.name,
                cls.classes,
                cls.department,
                cls.cpu_requests,
                cls.cpu_limits,
                cls.mem_requests,
                cls.mem_limits,
                cls.number,
                func.round(func.avg(QosServiceDate.livability), 2).label('livability'),
                func.round(func.max(QosServiceDate.cpu_usage_max), 2).label('cpu_usage_max'),
                func.round(func.min(QosServiceDate.cpu_usage_min), 2).label('cpu_usage_min'),
                func.round(func.avg(QosServiceDate.cpu_usage_avg), 2).label('cpu_usage_avg'),
                func.round(func.max(QosServiceDate.mem_usage_max), 2).label('mem_usage_max'),
                func.round(func.min(QosServiceDate.mem_usage_min), 2).label('mem_usage_min'),
                func.round(func.avg(QosServiceDate.mem_usage_avg), 2).label('mem_usage_avg')
            ).to_dict_first()
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return False, None

        return True, data